using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;

namespace FeedFly
{
    /// <summary>
    /// Static helper class for connecting to the database
    /// </summary>
    public static class Data
    {
        private static string connectionString;

        public static SqlCeResultSet ExecuteResultSet(string sql, SqlCeConnection conn)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

            cmd.Dispose();
            return rs;
        }  
      
        public static SqlCeDataReader ExecuteReader(string sql)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, GetConnection());
            SqlCeDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Dispose();
            return rdr;
        }

        public static SqlCeDataReader ExecuteReader(string sql, SqlCeConnection conn)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            SqlCeDataReader rdr = cmd.ExecuteReader();

            cmd.Dispose();
            return rdr;
        }

        public static SqlCeDataReader ExecuteReader(string tableName, string indexName, object[] start, SqlCeConnection conn)
        {
            SqlCeCommand cmd = new SqlCeCommand(tableName, conn);
            cmd.IndexName = indexName;
            cmd.CommandType = CommandType.TableDirect;

            cmd.SetRange(DbRangeOptions.Match, start, null);

            SqlCeDataReader rdr = cmd.ExecuteReader();

            cmd.Dispose();
            return rdr;
        }

        public static SqlCeDataReader ExecuteReader(string tableName, string indexName, object keyValue, SqlCeConnection conn, bool closeConnection)
        {
            SqlCeCommand cmd = new SqlCeCommand(tableName, conn);
            cmd.IndexName = indexName;
            cmd.CommandType = CommandType.TableDirect;

            CommandBehavior cmdBehav = (closeConnection) ? CommandBehavior.CloseConnection : CommandBehavior.Default;

            cmd.SetRange(DbRangeOptions.Match, new object[] {keyValue}, null);

            SqlCeDataReader rdr = cmd.ExecuteReader(cmdBehav);
            cmd.Dispose();
            return rdr;
        }

        public static SqlCeDataReader ExecuteReader(string tableName, string indexName, object keyValue)
        {
            return Data.ExecuteReader(tableName, indexName, keyValue, GetConnection(), true);
        }

        public static int ExecuteNonQuery(string sql)
        {
            SqlCeConnection conn = GetConnection();
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);

            int resultCount = cmd.ExecuteNonQuery();

            cmd.Dispose();
            conn.Close();
            conn.Dispose();

            return resultCount;            
        }

        public static int ExecuteNonQuery(string sql, SqlCeConnection conn)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);

            int resultCount = cmd.ExecuteNonQuery();

            cmd.Dispose();

            return resultCount;
        }

        public static int ExecuteNonQuery(string sql, SqlCeConnection conn, SqlCeTransaction trans)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, conn, trans);

            int resultCount = cmd.ExecuteNonQuery();

            cmd.Dispose();

            return resultCount;
        }

        public static object ExecuteScalar(string sql)
        {
            SqlCeConnection conn = GetConnection();
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            Object obj = cmd.ExecuteScalar();

            conn.Close();
            conn.Dispose();

            return obj;
        }

        public static object ExecuteScalar(string sql, SqlCeConnection conn)
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            Object obj = cmd.ExecuteScalar();

            cmd.Dispose();
            return obj;
        }

        public static int GetIdentity(SqlCeConnection conn)
        {
            return (int)(decimal)ExecuteScalar("SELECT @@IDENTITY", conn);
        }

        public static SqlCeConnection GetConnection()
        {
            SqlCeConnection conn = new SqlCeConnection(getConnectionString());            
            conn.Open();
            return conn;
        }

        private static string getConnectionString()
        {
            return connectionString;
        }

        private static ListDictionary zones = new ListDictionary();

        public static ListDictionary TimeZones
        {
            get { return zones; }
        }

        public static void Init()
        {
            // build connection string for application
            // use assembly reflection to determine current directory
            StringBuilder csbuilder = new StringBuilder("Data Source='", 100);
            csbuilder.Append(App.GetApplicationPath()).Append("ffdb.sdf';default lock timeout=5000;Password='password'");
            connectionString = csbuilder.ToString();   
         
            // initialize time zone list for date parsing
            zones.Add("UT", "+0000");
            zones.Add("EST", "-0500");
            zones.Add("EDT", "-0400");
            zones.Add("CST", "-0600");
            zones.Add("CDT", "-0500");
            zones.Add("MST", "-0700");
            zones.Add("MDT", "-0600");
            zones.Add("PST", "-0800");
            zones.Add("PDT", "-0700");
        }
        
    }
}
